package com.registerserver.dao;


import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.registerserver.model.Student;

public class StudentDaoImpl implements StudentDao{
	
	private Connection con=null;
	
	public StudentDaoImpl() {
			con = ConnectionFactory.createConnection();
	}
	
	@Override
	public Student getStudentInfoByID(String studentID) throws SQLException,IOException {
		String sql = "{call getStudentInfoByID(?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
//		call.execute();
		ResultSet result = call.executeQuery();
		Student student = new Student();
		if (result.next()){
				student.setID(result.getString("StudentID"));
				student.setFirstName(result.getString("StudentFirstName"));
				student.setLastName(result.getString("StudentLastName"));
				student.setBirthday(result.getDate("Birthday"));
				student.setSex(result.getString("StudentSex"));
				student.setClassName(result.getString("Class"));
				student.setFacultyID(result.getString("FacultyID"));
				student.setMajor(result.getString("Marjor"));
				student.setStartedYear(result.getDate("StartedYear"));
				student.setEndYear(result.getDate("EndYear"));
				student.setImageURL(result.getString("ImageURL"));
		}
		return student;
	}


	@Override
	public List<Student> getListStudent() throws SQLException {
		String sql = "{call getListStudent()}";
		CallableStatement call = con.prepareCall(sql);
		
		List<Student> list = new ArrayList<Student>();
		ResultSet result = call.executeQuery();
		
		if (result!=null){
			while(result.next()){
				Student student = new Student();
				student.setID(result.getString("StudentID"));
				student.setFirstName(result.getString("FirstName"));
				student.setLastName(result.getString("LastName"));
				student.setBirthday(result.getDate("Birthday"));
				student.setSex(result.getString("Sex"));
				student.setClassName(result.getString("ClassName"));
				student.setFacultyID(result.getString("FacultyID"));
				student.setMajor(result.getString("Major"));
				student.setStartedYear(result.getDate("StartedYear"));
				student.setEndYear(result.getDate("EndYear"));
				student.setImageURL(result.getString("ImageURL"));
				list.add(student);
			}
			return list;
		}
		return null;
	}

	// get amount of student have the same course id, to check when to open the class.
	@Override
	public int getAmountOfStudent(String courseID) throws SQLException {
		String sql = "{call getAmountOfStudent(?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, courseID);
		call.registerOutParameter(1,java.sql.Types.INTEGER);
		return call.getInt(1);
	}


	@Override
	public boolean addNewStudent(Student student) throws SQLException {
		String sql = "{call addNewStudent(?,?,?,?,?,?,?,?,?,?,?,?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1,student.getID());
		call.setString(2,student.getFirstName());
		call.setString(3, student.getLastName());
		call.setString(4,student.getSex());
		call.setDate(5, (java.sql.Date) student.getBirthday());
		call.setString(6, student.getHometown());
		call.setString(7, student.getMajor());
		call.setString(8, student.getClassName());
		call.setDate(9, (java.sql.Date) student.getStartedYear());
		call.setDate(10, (java.sql.Date) student.getEndYear());
		call.setString(11, student.getFacultyID());
		call.setString(12, student.getImageURL());
		return (call.executeUpdate()>0);
	}


	@Override
	public boolean deleteStudent(String studentID) throws SQLException {
		String sql = "{call deleteStudentByID(?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		return call.executeUpdate()>0;
	}


	@Override
	public boolean updateStudentInfo(Student studentUpdate) throws SQLException{
		
		String sql = "{call updateStudentInfo(?,?,?,?,?,?,?,?,?,?,?,?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1,studentUpdate.getID());
		call.setString(2,studentUpdate.getFirstName());
		call.setString(3, studentUpdate.getLastName());
		call.setString(4,studentUpdate.getSex());
		call.setDate(5, (java.sql.Date) studentUpdate.getBirthday());
		call.setString(6, studentUpdate.getHometown());
		call.setString(7, studentUpdate.getMajor());
		call.setString(8, studentUpdate.getClassName());
		call.setDate(9, (java.sql.Date) studentUpdate.getStartedYear());
		call.setDate(10, (java.sql.Date) studentUpdate.getEndYear());
		call.setString(11, studentUpdate.getFacultyID());
		call.setString(12, studentUpdate.getImageURL());
		return (call.executeUpdate()>0);
	}

	@Override
	public String getStudentName(String studentID) throws SQLException {
		String studentName = "";
		String sql = "call getStudentName(?)";
		
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		ResultSet result = call.executeQuery();
		if (result!=null){
			while(result.next()){
				studentName = result.getString("LoginID");
			}
		}
		return studentName;
	}
	
}
